#!/usr/bin/python
#coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
import datetime
from sqlalchemy.pool import NullPool
from common import db_utils as util
import sys

dtypedict = {'ACCT': INTEGER,
            'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,'SHIFT': VARCHAR,
            'PRODUCE_TIME': VARCHAR,'PROD_COILNO': VARCHAR,'ENTRY_COILNO': VARCHAR,'STEELNO': VARCHAR,
            'ACT_TOTAL_COST': DECIMAL,'MAIN_MATERIAL_COST': DECIMAL,'ENERGYCONSUM_COST': DECIMAL,
            'ACCES_COST': DECIMAL,'ROLL_COST': DECIMAL,'OTHER_COST': DECIMAL,
            'COST_6': DECIMAL,'COST_7': DECIMAL,'COST_8': DECIMAL,
            'ACT_OUTPUT_WT': DECIMAL,'ACT_INPUT_WT': DECIMAL,'THICK': VARCHAR,'THICK_CODE': VARCHAR,
            'WIDTH': VARCHAR, 'WIDTH_CODE': VARCHAR, 'ANNEAL_CURVE': VARCHAR, 'TRIM_FLAG': VARCHAR,
            'ENTRY_WIDTH': VARCHAR, 'ENTRY_WIDTH_CODE': VARCHAR, 'ENTRY_THICK': VARCHAR, 'ENTRY_THICK_CODE': VARCHAR,
            'PLAN_NO_C': VARCHAR, 'PLAN_NO_CODE': VARCHAR, 'TRIMMING_WIDTH_RANGE': VARCHAR, 'TRIMMING_WIDTH_CODE': VARCHAR,
            'IN_MAT_DIV_CODE': VARCHAR, 'IN_MAT_DIV': VARCHAR, 'PRODUCE_NICK_FLAG': VARCHAR, 'PROD_CAT_CODE': VARCHAR,
            'PROD_CAT': VARCHAR, 'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()

def save_as_table(df,table_name,conn,schema_name=None,dtypedict=None):
    return pd.io.sql.to_sql(df, table_name, conn,schema_name,if_exists='append',index=False,chunksize=10000,dtype=dtypedict)

def toCD0106(unit_code,p_date,p_data_type):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_CD0106 WHERE DATE  =  '""" + p_date + """'"""
    conn_rds.execute(del_sql)

    sql = """SELECT 
                DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,'""" + p_date + """' AS DATE,
                    CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (ACT_INPUT_WT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS ACT_TOTAL_COST,
                 CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0.0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)+
                CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_STD_AMT-ACT_OUTPUT_WT)*STD_TON_STEEL_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END)
                 END AS TARGET_TOTAL_COST,
                 CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS ACT_CHANGE_COST
                ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('1','4','5','6') AND LEFT(COST_SUBJECT,2)<>'15' THEN COALESCE(UNIT_PROC_STD_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END AS TARGET_CHANGE_COST
                  ,
                  CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                  ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' AND ACT_INPUT_WT<>0 THEN (COST_SUBJECT_ON_AMT-ACT_OUTPUT_WT)*COST_SUBJECT_PRICE ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) END  AS MAIN_MATERIAL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('4','5') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ENERGYCONSUM_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('6') AND LEFT(COST_SUBJECT,2) NOT IN ('64') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ACCES_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,1) IN ('3') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS ROLL_COST
                  ,CASE WHEN SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0) ELSE 0 END) = 0 THEN 0
                                ELSE CAST(SUM(CASE WHEN LEFT(COST_SUBJECT,2) NOT IN ('15') AND LEFT(COST_SUBJECT,1) IN ('1') THEN COALESCE(UNIT_PROC_ON_AMT,0) ELSE 0 END) AS DOUBLE)/SUM(CASE WHEN LEFT(COST_SUBJECT,2)='15' THEN COALESCE(ACT_OUTPUT_WT,0)ELSE 0 END) END AS OTHER_COST
                  ,0.0 AS COST_6
                  ,0.0 AS COST_7
                  ,0.0 AS COST_8,
                  'BGRAGGCB' AS REC_CREATOR,
                  TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                  '' AS REC_REVISOR,
                  TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
            FROM BGTARAS1.T_ADS_FACT_SICB_CA0001  WHERE ACCT='1001' AND UNIT_CODE='""" + unit_code + """' 
                    AND DATA_TYPE='""" + p_data_type + """'
                    AND to_char(TO_TIMESTAMP('""" + p_date + """'||'200000','YYYYMMDDHH24MISS')-1 days ,'YYYYMMDD')||'220000' <= PRODUCE_TIME and PRODUCE_TIME <
                '""" + p_date + """'||'220000'
                    GROUP BY ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE
                """
    print(sql)
    df_plan = util.query(conn_rds, sql)
    util.insert(df_plan, conn_rds, "T_ADS_FACT_SICB_CD0106","BGTARAS1",  dtypedict)

def CD0106(unit_code,date,p_data_type):
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    toCD0106(unit_code, date, p_data_type)
    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    start_time = (now_time - datetime.timedelta(minutes=360)).strftime("%Y%m%tmp_dict%H%M%S")
    end_time = now_time.strftime("%Y%m%tmp_dict%H%M%S")
    # start_time = '20210508200000'
    # end_time = '20210509200000'
    global conn_db2,conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    toCD0106("Q418",'20210811','D')
    conn_rds.close()
    conn_db2.close()
    sys.exit()